package dao;

import entity.Address;
import entity.Orders;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import relationEntity.CollectionProduct;
import relationEntity.OrderAddress;
import relationEntity.OrderProduct;
import relationEntity.UserAddress;

import java.util.List;

/**
 * Created by jifeng on 2016/12/15 0015.
 */
public class OrderDao {

    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }
    public SessionFactory getSessionFactory() {
        return sessionFactory;
    }

    private Session getCurrentSession() {
        return sessionFactory.getCurrentSession();
    }


    public List<Orders> orderList(int offset,String paymentStatus,String sendStatus,String orderCode,String userId, int length){
        List<Orders> ordersList =null;
        try {
            String sql ="select \n" +
                    "o.id,\n" +
                    "o.orderCode,\n" +
                    "o.startDate,\n" +
                    "o.paymentStatus,\n" +
                    "o.paymentTime,\n" +
                    "o.toExamineStatus,\n" +
                    "o.sendStatus,\n" +
                    "o.orderType,\n" +
                    "o.paymentType,\n" +
                    "o.invoice,\n" +
                    "o.totalMoney ";
            String exceptSelectSql =" from Orders o \n" +
                    " where o.deletes=0";

            if (userId!=null&userId!=""){
                exceptSelectSql+=" AND o.userId ="+userId+"";
            }


            if (paymentStatus !=null&paymentStatus!=""){
                if (Integer.valueOf(paymentStatus)==1){
                    exceptSelectSql+=" AND o.paymentStatus='已付款'";
                }
                if (Integer.valueOf(paymentStatus)==0){
                    exceptSelectSql+=" AND o.paymentStatus='未付款'";
                }
            }

            if (sendStatus!=null & sendStatus!=""){
                if (Integer.valueOf(sendStatus)==1){
                    exceptSelectSql+=" AND o.sendStatus='已发货'";
                }
                if (Integer.valueOf(sendStatus)==0){
                    exceptSelectSql+=" AND o.sendStatus='未发货'";
                }
            }

            if (orderCode!=null&orderCode!=""){
                exceptSelectSql+=" AND o.orderCode LIKE '%"+orderCode+"%'";
            }

            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("orderCode",StandardBasicTypes.STRING);
            query.addScalar("startDate",StandardBasicTypes.DATE);
            query.addScalar("paymentStatus",StandardBasicTypes.STRING);
            query.addScalar("paymentTime",StandardBasicTypes.DATE);
            query.addScalar("toExamineStatus",StandardBasicTypes.STRING);
            query.addScalar("sendStatus",StandardBasicTypes.STRING);
            query.addScalar("orderType",StandardBasicTypes.STRING);
            query.addScalar("paymentType",StandardBasicTypes.STRING);
            query.addScalar("invoice",StandardBasicTypes.INTEGER);
            query.addScalar("totalMoney",StandardBasicTypes.LONG);
            query.setResultTransformer(Transformers.aliasToBean(Orders.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            ordersList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return ordersList;
    }

    public int getCancelOrderAllRowCount(String paymentStatus,String sendStatus,String orderCode) {
        String sql ="select \n" +
                "o.id,\n" +
                "o.orderCode,\n" +
                "o.startDate,\n" +
                "o.paymentStatus,\n" +
                "o.paymentTime,\n" +
                "o.toExamineStatus,\n" +
                "o.sendStatus,\n" +
                "o.orderType,\n" +
                "o.paymentType,\n" +
                "o.invoice,\n" +
                "o.totalMoney ";
        String exceptSelectSql =" from Orders o \n" +
                " where o.deletes=1";
        if (paymentStatus !=null&paymentStatus!=""){
            if (Integer.valueOf(paymentStatus)==1){
                exceptSelectSql+=" AND o.paymentStatus='已付款'";
            }if (Integer.valueOf(paymentStatus)==0){
                exceptSelectSql+=" AND o.paymentStatus='未付款'";
            }
        }

        if (sendStatus!=null & sendStatus!=""){
            if (Integer.valueOf(sendStatus)==1){
                exceptSelectSql+=" AND o.sendStatus='已发货'";
            }
            if (Integer.valueOf(sendStatus)==0){
                exceptSelectSql+=" AND o.sendStatus='未发货'";
            }
        }

        if (orderCode!=null&orderCode!=""){
            exceptSelectSql+=" AND o.orderCode LIKE '%"+orderCode+"%'";
        }        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public int getOrderAllRowCount(String paymentStatus,String sendStatus,String orderCode,String userId) {
        String sql ="select \n" +
                "o.id,\n" +
                "o.orderCode,\n" +
                "o.startDate,\n" +
                "o.paymentStatus,\n" +
                "o.paymentTime,\n" +
                "o.toExamineStatus,\n" +
                "o.sendStatus,\n" +
                "o.orderType,\n" +
                "o.paymentType,\n" +
                "o.invoice,\n" +
                "o.totalMoney ";
        String exceptSelectSql =" from Orders o \n" +
                                " where o.deletes=0";

        if (userId!=null&userId!=""){
            exceptSelectSql+=" AND o.userId ="+userId+"";
        }

        if (paymentStatus !=null&paymentStatus!=""){
            if (Integer.valueOf(paymentStatus)==1){
                exceptSelectSql+=" AND o.paymentStatus='已付款'";
            }if (Integer.valueOf(paymentStatus)==0){
                exceptSelectSql+=" AND o.paymentStatus='未付款'";
            }
        }

        if (sendStatus!=null & sendStatus!=""){
            if (Integer.valueOf(sendStatus)==1){
                exceptSelectSql+=" AND o.sendStatus='已发货'";
            }
            if (Integer.valueOf(sendStatus)==0){
                exceptSelectSql+=" AND o.sendStatus='未发货'";
            }
        }

        if (orderCode!=null&orderCode!=""){
            exceptSelectSql+=" AND o.orderCode LIKE '%"+orderCode+"%'";
        }
        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public int getOrderAddressAllRowCount(String orderCode,String paymentStatus) {
        String sql="select o.id,o.orderCode,o.startDate,o.paymentStatus,o.paymentTime,a.detailed";
        String exceptSelectSql =" from Orders o,Address a where o.deletes =0";

        if (paymentStatus !=null&paymentStatus!=""){
            if (Integer.valueOf(paymentStatus)==1){
                exceptSelectSql+=" AND o.paymentStatus='已付款'";
            }if (Integer.valueOf(paymentStatus)==0){
                exceptSelectSql+=" AND o.paymentStatus='未付款'";
            }
        }
        if (orderCode!=null&orderCode!=""){
            exceptSelectSql+=" AND o.orderCode LIKE '%"+orderCode+"%'";
        }

        return getCurrentSession().createSQLQuery(sql+exceptSelectSql).list().size();
    }

    public List<Orders> cancelOrderList(int offset,String paymentStatus,String sendStatus,String orderCode, int length){
        List<Orders> ordersList =null;
        try {
            String sql ="select \n" +
                    "o.id,\n" +
                    "o.orderCode,\n" +
                    "o.startDate,\n" +
                    "o.paymentStatus,\n" +
                    "o.paymentTime,\n" +
                    "o.toExamineStatus,\n" +
                    "o.sendStatus,\n" +
                    "o.orderType,\n" +
                    "o.paymentType,\n" +
                    "o.invoice,\n" +
                    "o.totalMoney ";
            String exceptSelectSql =" from Orders o \n" +
                    " where o.deletes=1";
            if (paymentStatus !=null&paymentStatus!=""){
                if (Integer.valueOf(paymentStatus)==1){
                    exceptSelectSql+=" AND o.paymentStatus='已付款'";
                }
                if (Integer.valueOf(paymentStatus)==0){
                    exceptSelectSql+=" AND o.paymentStatus='未付款'";
                }
            }

            if (sendStatus!=null & sendStatus!=""){
                if (Integer.valueOf(sendStatus)==1){
                    exceptSelectSql+=" AND o.sendStatus='已发货'";
                }
                if (Integer.valueOf(sendStatus)==0){
                    exceptSelectSql+=" AND o.sendStatus='未发货'";
                }
            }

            if (orderCode!=null&orderCode!=""){
                exceptSelectSql+=" AND o.orderCode LIKE '%"+orderCode+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id", StandardBasicTypes.INTEGER);
            query.addScalar("orderCode",StandardBasicTypes.STRING);
            query.addScalar("startDate",StandardBasicTypes.DATE);
            query.addScalar("paymentStatus",StandardBasicTypes.STRING);
            query.addScalar("paymentTime",StandardBasicTypes.DATE);
            query.addScalar("toExamineStatus",StandardBasicTypes.STRING);
            query.addScalar("sendStatus",StandardBasicTypes.STRING);
            query.addScalar("orderType",StandardBasicTypes.STRING);
            query.addScalar("paymentType",StandardBasicTypes.STRING);
            query.addScalar("invoice",StandardBasicTypes.INTEGER);
            query.addScalar("totalMoney",StandardBasicTypes.LONG);
            query.setResultTransformer(Transformers.aliasToBean(Orders.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            ordersList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return ordersList;
    }


    public List<OrderAddress> orderAddressList(int offset,String orderCode,String paymentStatus, int length){
        List<OrderAddress> orderAddressList =null;
        try {

            String sql="select o.id,o.orderCode,o.startDate,o.paymentStatus,o.paymentTime,o.toExamineStatus,o.sendStatus,o.orderType,o.paymentType,o.invoice,o.totalMoney,a.detailed ";
            String exceptSelectSql=" from Orders o,Address a where o.deletes =0";

            if (paymentStatus !=null&paymentStatus!=""){
                if (Integer.valueOf(paymentStatus)==1){
                    exceptSelectSql+=" AND o.paymentStatus='已付款'";
                }
                if (Integer.valueOf(paymentStatus)==0){
                    exceptSelectSql+=" AND o.paymentStatus='未付款'";
                }
            }
            if (orderCode!=null&orderCode!=""){
                exceptSelectSql+=" AND o.orderCode LIKE '%"+orderCode+"%'";
            }
            SQLQuery query = getCurrentSession().createSQLQuery(sql+exceptSelectSql);
            query.addScalar("id",StandardBasicTypes.INTEGER);
            query.addScalar("orderCode",StandardBasicTypes.STRING);
            query.addScalar("startDate",StandardBasicTypes.DATE);
            query.addScalar("paymentStatus",StandardBasicTypes.STRING);
            query.addScalar("paymentTime",StandardBasicTypes.DATE);
            query.addScalar("paymentTime",StandardBasicTypes.DATE);
            query.addScalar("toExamineStatus",StandardBasicTypes.STRING);
            query.addScalar("sendStatus",StandardBasicTypes.STRING);
            query.addScalar("orderType",StandardBasicTypes.STRING);
            query.addScalar("paymentType",StandardBasicTypes.STRING);
            query.addScalar("detailed",StandardBasicTypes.STRING);
            query.addScalar("invoice",StandardBasicTypes.INTEGER);
            query.addScalar("totalMoney",StandardBasicTypes.LONG);
            query.setResultTransformer(Transformers.aliasToBean(OrderAddress.class));
            query.setFirstResult(offset);
            query.setMaxResults(length);
            orderAddressList =query.list();
        }catch (RuntimeException er){
            throw er;
        }
        return orderAddressList;
    }

    public void deleteOrder(String ids){
        getCurrentSession().createSQLQuery("update orders o SET o.deletes = 1 WHERE  o.id IN ("+ids+")").executeUpdate();
    }

    public OrderAddress orderDetails(int id){
        List<OrderAddress> orderAddressList =null;
        String sql="select o.id,o.orderCode,o.startDate,o.paymentStatus,o.paymentTime,o.toExamineStatus,o.sendStatus,o.orderType,o.paymentType,o.invoice,o.totalMoney,a.detailed from Orders o,Address a where o.id ="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id",StandardBasicTypes.INTEGER);
        query.addScalar("orderCode",StandardBasicTypes.STRING);
        query.addScalar("startDate",StandardBasicTypes.DATE);
        query.addScalar("paymentStatus",StandardBasicTypes.STRING);
        query.addScalar("paymentTime",StandardBasicTypes.DATE);
        query.addScalar("paymentTime",StandardBasicTypes.DATE);
        query.addScalar("toExamineStatus",StandardBasicTypes.STRING);
        query.addScalar("sendStatus",StandardBasicTypes.STRING);
        query.addScalar("orderType",StandardBasicTypes.STRING);
        query.addScalar("paymentType",StandardBasicTypes.STRING);
        query.addScalar("detailed",StandardBasicTypes.STRING);
        query.addScalar("invoice",StandardBasicTypes.INTEGER);
        query.addScalar("totalMoney",StandardBasicTypes.LONG);
        query.setResultTransformer(Transformers.aliasToBean(OrderAddress.class));
        orderAddressList =query.list();
        return orderAddressList.get(0);
    }

    public void update(Orders orders){
        String sql="UPDATE orders o SET  o.orderCode= '"+orders.getOrderCode()+"',o.paymentStatus='"+orders.getPaymentStatus()+"',o.toExamineStatus='"+orders.getToExamineStatus()+"',o.sendStatus='"+orders.getSendStatus()+"',o.orderType='"+orders.getOrderType()+"',o.paymentType='"+orders.getPaymentType()+"',o.invoice="+orders.getInvoice()+",o.totalMoney="+orders.getTotalMoney()+" WHERE o.id="+orders.getId()+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public Address findAddressById(int id){
        Address address = new Address();
        int addId=findOrderById(id).getId();
        List<Address> addressList = getCurrentSession().createSQLQuery("SELECT * FROM address a WHERE a.id="+addId+"").addEntity(Address.class).list();
        address=addressList.get(0);
        return address;
    }

    public Orders findOrderById(int id){
        Orders orders= new Orders();
        List<Orders> ordersList=getCurrentSession().createSQLQuery("SELECT * FROM orders o WHERE o.id="+id+"").addEntity(Orders.class).list();
        orders=ordersList.get(0);
        return orders;
    }

    public void updateAddress(Address address,String detailed){
        String sql="UPDATE address a SET a.detailed ='"+detailed+"' WHERE a.id="+address.getId()+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }

    public void recoveryOrder(String ids){
        getCurrentSession().createSQLQuery("update orders o SET o.deletes = 0 WHERE  o.id IN ("+ids+")").executeUpdate();
    }

    public List<Address> findUserAddressByUserId(int offset,int id, int length){
        List<Address> addressList=null;
        String sql="SELECT\n" +
                "address.id,\n" +
                "address.startDate,\n" +
                "address.province,\n" +
                "address.city,\n" +
                "address.area,\n" +
                "address.detailed,\n" +
                "address.userId,\n" +
                "address.addressee,\n" +
                "address.addressPhone,\n" +
                "address.zipCode,\n" +
                "address.deletes\n" +
                "FROM address \n" +
                "LEFT JOIN user ON user.addressId = address.id\n" +
                "WHERE address.deletes = 0 AND address.userId="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.setResultTransformer(Transformers.aliasToBean(Address.class));
        query.setFirstResult(offset);
        query.setMaxResults(length);
        addressList =query.list();
        return addressList;
    }

    public List<Address> findUserAddressByUserIds(int id){
        List<Address> addressList=null;
        String sql="SELECT * FROM address WHERE address.deletes = 0 AND address.userId="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.setResultTransformer(Transformers.aliasToBean(Address.class));
        addressList =query.list();
        return addressList;
    }

    public Address findUserAddressById(int id){
        List<Address> addressList=null;
        String sql="SELECT * FROM address WHERE address.id="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.setResultTransformer(Transformers.aliasToBean(Address.class));
        addressList =query.list();
        return addressList.get(0);
    }

    public void addAddress(Address address){
        getCurrentSession().save(address);
    }

    public void addOrder(Orders orders){
        getCurrentSession().save(orders);
    }
    public void updateOrder(String orderCode,int orderId){
        getCurrentSession().createSQLQuery("UPDATE orders SET orders.orderCode="+orderCode+" WHERE orders.id ="+orderId+"").executeUpdate();
    }

    public List<OrderProduct> findOrdersListByUserId(int id){
        List<OrderProduct> orderProductList=null;
        String sql="SELECT o.id,o.orderCode,o.sendStatus,o.paymentStatus,o.num,o.totalMoney,p.productName,p.nowPrice,p.carousel0,o.productId \n" +
                " FROM orders o\n" +
                "\tLEFT JOIN product AS p ON p.id =o.productId\n" +
                " WHERE o.userId="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id",StandardBasicTypes.INTEGER);
        query.addScalar("orderCode",StandardBasicTypes.STRING);
        query.addScalar("sendStatus",StandardBasicTypes.STRING);
        query.addScalar("paymentStatus",StandardBasicTypes.STRING);
        query.addScalar("num",StandardBasicTypes.INTEGER);
        query.addScalar("totalMoney",StandardBasicTypes.LONG);
        query.addScalar("productName",StandardBasicTypes.STRING);
        query.addScalar("nowPrice",StandardBasicTypes.LONG);
        query.addScalar("carousel0",StandardBasicTypes.STRING);
        query.addScalar("productId",StandardBasicTypes.INTEGER);
        query.setResultTransformer(Transformers.aliasToBean(OrderProduct.class));
        orderProductList=query.list();
        return orderProductList;
    }

    public List<CollectionProduct> findCollection(int id){
        List<CollectionProduct> collectionProductList=null;
        String sql="SELECT p.id,p.carousel0,p.productName,p.nowPrice,p.basicsNum \n" +
                " FROM collection c\n" +
                "\tLEFT JOIN product AS p ON p.id = c.productId\n" +
                " WHERE c.userId="+id+"";
        SQLQuery query = getCurrentSession().createSQLQuery(sql);
        query.addScalar("id",StandardBasicTypes.INTEGER);
        query.addScalar("carousel0",StandardBasicTypes.STRING);
        query.addScalar("productName",StandardBasicTypes.STRING);
        query.addScalar("nowPrice",StandardBasicTypes.LONG);
        query.addScalar("basicsNum",StandardBasicTypes.LONG);
        query.setResultTransformer(Transformers.aliasToBean(CollectionProduct.class));
        collectionProductList= query.list();
        return collectionProductList;
    }

    public void updateAddresses(int userId,int addressId,String addressee,String addressPhone,String zipCode,String detailed){
        String sql="UPDATE address a SET a.detailed='"+detailed+"',a.userId="+userId+",a.addressee='"+addressee+"',a.addressPhone='"+addressPhone+"',a.zipCode='"+zipCode+"' WHERE a.id="+addressId+"";
        getCurrentSession().createSQLQuery(sql).executeUpdate();
    }


    public int getUserAddressAllRowCount(int id) {
        String sql="SELECT * FROM address WHERE address.deletes = 0 AND address.userId="+id+"";
        return getCurrentSession().createSQLQuery(sql).list().size();
    }
}
